package csv2db;

import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

import com.mysql.jdbc.Statement;

public class CSV2SQL {
	
	protected Connection con;
	protected String url;
	protected String username;
	protected String password;
	protected String driver;
	
	public CSV2SQL(){
		
		con = null;
	    url = "jdbc:mysql://localhost/database_files";
	    username = "root";
	    password = "";
	    driver = "com.mysql.jdbc.Driver";
		
	}
	
	
	
public void create_table(){
	try{
		 Class.forName(driver).newInstance();
		 con = DriverManager.getConnection(url, username, password);
		 
		 try{
			 Statement st = (Statement) con.createStatement();
			 String table = "CREATE TABLE data(id_files INTEGER NOT NULL AUTO_INCREMENT, fileName VARCHAR(255), size INTEGER,PRIMARY KEY(id_files))";
			 st.executeUpdate(table);
			 System.out.println("Tabella creata con successo!");
			 
		 }catch(SQLException s){
			 
			 System.out.println("La tabella esiste gia`!");
		  }
		  	con.close();
		  
	}catch (Exception e){
		
			e.printStackTrace();
	 }
}

public void drop_table(){
	
	Statement stmt;
    
	try{
		Class.forName(driver).newInstance();
		con = DriverManager.getConnection(url, username, password);
		try{
			stmt = (Statement) con.createStatement();
			String query = "DROP Table data";
			stmt.executeUpdate(query);
			System.out.println("Tabella cancellata con successo");                            
         } catch(SQLException s){
        	 System.out.println("La tabella non e` stata cancellata");               
        	// s.printStackTrace();
         }
            con.close();
            
	}catch (Exception e){
		e.printStackTrace();
		if(e.getMessage().equals("Unknown table 'data'")){
			
			create_table();
		}
		
	}

}
	
	
	

	
public String takeOffQuotes(String input_str){
		
		if (input_str.startsWith("\"")){
			input_str = input_str.substring(1, input_str.length());
	    }
	    if (input_str.endsWith("\"")){
	    	input_str = input_str.substring(0, input_str.length() - 1);
	    }
	    
	    return input_str;
	}
	
	
	// parsing the result of slk commands fls + mactime
	public void parserCsv(File filename) throws FileNotFoundException{
		
//        Connection con = null;
        PreparedStatement ps = null;
//        url = "jdbc:mysql://localhost/database_files";
//	    String username = "root";
//	    String password = "";
//        String driver = "com.mysql.jdbc.Driver";
		
		Scanner lineScan = new Scanner(filename);
		Scanner s = new Scanner(lineScan.nextLine());
		
		System.out.println("BEFORE TRY/CATCH");
		
		try {
//          TEST
            drop_table();
            create_table();
            Class.forName(driver);
            con = DriverManager.getConnection(url, username, password);
            con.setAutoCommit(false);
            String query = "INSERT INTO ?(fileName, size) VALUES(?, ?)";
            ps = con.prepareStatement(query);
            
//	        FOTTUTO BUG            
            System.out.println(lineScan.hasNextLine());
		
		while (lineScan.hasNextLine()) {

			s = new Scanner(lineScan.nextLine());
			
			s.useDelimiter("\n");
			
//			System.out.println("BEFORE WHILE2");
			
			while (s.hasNext()) {
				
				String temp = s.next();
				
				String[] data = temp.split(",");
				
				String fileName = data[data.length-1];
				// take off quotes
				Integer size = Integer.valueOf(data[1]);
				//System.out.println(size);
				
				ps.setString(1, takeOffQuotes(fileName));
				ps.setDouble(2, size);
				ps.addBatch();
				
//				System.out.println("DAI CAZZO!!");
				
			}
		}
		
		ps.executeBatch();
		con.commit();
		//ps.close();
        con.close();
		
	} catch(Exception e) {
		e.printStackTrace();
	}
	
	}
}